************* NOTE: pause Dropbox synching before running!
	/* right-click Dropbox icon, left-click bottom left of the window */

	
	
**********************
* Monthly sentiments *
**********************

* Import country codes
	foreach x in all oecd {
		clear all
		import excel "orig/country_codes_`x'.xlsx", firstrow
		destring cou country, replace
		save "data/country_codes_`x'.dta",replace
	}
* Import data from Excel to Stata and set time
	// OECD
		foreach x in oecd_cci_updated oecd_bci ///
		{
			clear all
			import excel "orig/sentiment/`x'.xlsx", sheet("final_series") firstrow
			destring year month, replace
			gen time = ym(year, month) 
			format time %tm
			drop year_month date
			save "data/`x'.dta", replace
		}
	// US investor sentiment
		clear all
		import excel "orig/sentiment/investor_sentiment.xlsx", sheet("final_series") firstrow
		gen year = year(date)
		gen month = month(date)
		gen time = ym(year, month) 
		format time %tm
		drop year_month date
		save "data/investor_sentiment.dta", replace
		
* Merge series with country names conversion list
	clear all
	use "data/country_codes_oecd.dta"
	foreach x in oecd_cci_updated oecd_bci ///
	{
		merge m:m cou using "data/`x'.dta"
		drop _merge
	}
	save "data/local_sentiments_panel_m.dta", replace
	// merge with country codes conversion file
		drop country
		merge m:m cou using "data/country_codes_all.dta"
		drop _merge
		save "data/local_sentiments_panel_m.dta", replace

	
* Extract main sentiment measures
	******** NOTE: replace "G-7" with "G7" in both Excel files
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
	clear all
	use "data/oecd_`y'.dta"
	keep if cou=="`x'"
	collapse `y', by(time year)
	save "data/oecd_`y'_`x'.dta", replace
	}
	}

* Merge main sentiment measures
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
		merge m:m time using "data/oecd_`y'_`x'.dta"
		rename `y' `y'_`x'
		drop _merge
	}
	}
	save "data/main_sentiments_m.dta", replace

* Delete single data sets
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
		erase "data/oecd_`y'_`x'.dta"
	}
	}

* Correlation among main sentiments
	clear all
	use "data/main_sentiments_m.dta"
	pwcorr cci* bci*, sig // full sample
	pwcorr cci* bci* if year > 2004, sig // after 2003
	pwcorr cci*, sig // consumer confidence only
	pwcorr bci*, sig // business confidence only



*********************
* Annual sentiments *
*********************

* Local sentiment
	// take annual averages of local sentiments
		clear all
		use "data/local_sentiments_panel_m.dta"
		// take average by country and year
		foreach x in cci bci ///
		{
			egen `x'_y = mean(`x'), by (country year)
		}

	// collapse by year
		collapse cci_y bci_y, by(country cou year)
		foreach x in cci bci ///
		{
			rename `x'_y `x'
		}
		save "data/local_sentiments_panel_y.dta", replace
		// merge with country codes conversion file
			drop country
			merge m:m cou using "data/country_codes_all.dta", keep(match master)
			drop _merge
			save "data/local_sentiments_panel_y.dta", replace
		
* Investor sentiment
	// take average by year
		clear all
		use "data/investor_sentiment.dta"
		foreach x in bw1r bw2r ///
		{
			egen `x'_y = mean(`x'), by (year)
		}
	// collapse by year
		collapse bw1r_y bw2r_y, by(year)
		foreach x in bw1r bw2r ///
		{
			rename `x'_y `x'
		}
	save "data/investor_sentiment_y.dta", replace

* Extract main sentiment measures
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
		clear all
		use "data/oecd_`y'.dta"
		keep if cou=="`x'"
		collapse `y', by(year)
		save "data/oecd_`y'_`x'.dta", replace
	}
	}

* Merge main sentiment measures:
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
		merge m:m year using "data/oecd_`y'_`x'.dta"
		rename `y' `y'_`x'
		drop _merge
	}
	}
	merge m:m year using "data/investor_sentiment_y.dta"
	drop _merge
	save "data/main_sentiments_y.dta", replace
	clear all

* Delete single data sets
	foreach x in USA EA19 G7 OECD OECDE {
	foreach y in cci bci {
		erase "data/oecd_`y'_`x'.dta"
	}
	}
	erase "data/investor_sentiment_y.dta"

* Correlation among main sentiments
	clear all
	use "data/main_sentiments_y.dta"
	pwcorr cci* bci*, sig // confidence indices
	pwcorr bw* cci* bci*, sig // with investor sentiment
	pwcorr cci* bci* if year > 2004, sig // after 2003
	pwcorr cci*, sig // consumer confidence only
	pwcorr bci*, sig // business confidence only

	

****************************
* Local sentiment measures *
****************************

* Extract main sentiment measures for CCI
	foreach x in AUS AUT BEL BRA CAN CHE CHN CZE ///
	DEU DNK ESP EST FIN FRA GBR GRC ///
	HUN IDN IRL ISR ITA JPN KOR LUX ///
	MEX NLD NZL POL PRT RUS SVK SVN ///
	SWE TUR USA ZAF {
		foreach y in cci {
			clear all
			use "data/local_sentiments_panel_y.dta"
			keep if cou=="`x'"
			collapse cci, by(year)
			save "data/local_sentiments_time_cci_y_`x'.dta", replace
		}
		}
* Merge CCI measures
	foreach x in AUS AUT BEL BRA CAN CHE CHN CZE ///
	DEU DNK ESP EST FIN FRA GBR GRC ///
	HUN IDN IRL ISR ITA JPN KOR LUX ///
	MEX NLD NZL POL PRT RUS SVK SVN ///
	SWE TUR USA ZAF {
		foreach y in cci {
			merge m:m year using "data/local_sentiments_time_cci_y_`x'.dta"
			rename `y' `y'_`x'
			drop _merge
		}
		}
	save "data/local_sentiments_time_cci_y.dta", replace

* Delete single data sets
	foreach x in AUS AUT BEL BRA CAN CHE CHN CZE ///
	DEU DNK ESP EST FIN FRA GBR GRC ///
	HUN IDN IRL ISR ITA JPN KOR LUX ///
	MEX NLD NZL POL PRT RUS SVK SVN ///
	SWE TUR USA ZAF {
		foreach y in cci {
			erase "data/local_sentiments_time_cci_y_`x'.dta"
		}
		}
		
* Extract main sentiment measures for BCI
	foreach x in AUS AUT BEL BRA CAN CHE CHL CHN ///
	CZE DEU DNK ESP EST FIN FRA GBR ///
	GRC HUN IDN IND IRL ISR ITA JPN ///
	KOR LUX MEX NLD NOR NZL POL PRT ///
	RUS SVK SVN SWE TUR USA ZAF {
		foreach y in bci {
			clear all
			use "data/local_sentiments_panel_y.dta"
			keep if cou=="`x'"
			collapse bci, by(year)
			save "data/local_sentiments_time_bci_y_`x'.dta", replace
		}
		}

* Merge CCI measures
	foreach x in AUS AUT BEL BRA CAN CHE CHL CHN ///
	CZE DEU DNK ESP EST FIN FRA GBR ///
	GRC HUN IDN IND IRL ISR ITA JPN ///
	KOR LUX MEX NLD NOR NZL POL PRT ///
	RUS SVK SVN SWE TUR USA ZAF {
		foreach y in bci {
			merge m:m year using "data/local_sentiments_time_bci_y_`x'.dta"
			rename `y' `y'_`x'
			drop _merge
		}
		}
	save "data/local_sentiments_time_bci_y.dta", replace

* Delete single data sets
	foreach x in AUS AUT BEL BRA CAN CHE CHL CHN ///
	CZE DEU DNK ESP EST FIN FRA GBR ///
	GRC HUN IDN IND IRL ISR ITA JPN ///
	KOR LUX MEX NLD NOR NZL POL PRT ///
	RUS SVK SVN SWE TUR USA ZAF {
		foreach y in bci {
			erase "data/local_sentiments_time_bci_y_`x'.dta"
		}
		}

* Merge CCI and BCI local sentiments
	clear all
	use "data/local_sentiments_time_bci_y.dta"
	merge m:m year using "data/local_sentiments_time_cci_y.dta"
	drop _merge
	save "data/local_sentiments_time_y.dta", replace
	erase "data/local_sentiments_time_bci_y.dta"
	erase "data/local_sentiments_time_cci_y.dta"

/* Correlation matrix among local sentiments
	clear all
	use "data/local_sentiments_time_y.dta"
	// General
		pwcorr cci* bci*, sig // investor sentiment, full sample
		pwcorr cci* bci* if year > 2004, sig // investor sentiment, after 2003
	// Specific measures
		pwcorr cci_USA cci* bci*, sig // investor sentiment, full sample
		pwcorr cci_USA cci* bci* if year > 2004, sig // investor sentiment, after 2003
*/



******************************
* Annual sentiment: December *
******************************

// take December value of local sentiments
	clear all
	use "data/local_sentiments_panel_m.dta"
	
// create dummy that takes on value one in December
	gen m12 = 1 if month == 12
// calculate sentiment in December
		foreach x in cci bci ///
	{
		gen `x'_m12 = `x' * m12
	}
// for each firm-year, replace the missing values with the one from December
		foreach x in cci bci ///
	{
		bysort cou year (`x'_m12): replace `x'_m12 = `x'_m12[1] 
	}
		* now every monthly observation is associated with an annual measure of sentiment
// collapse to annual values
	collapse cci_m12 bci_m12, by(cou year)
// save main data set after the merger
	save "data/local_sentiments_panel_y_m12.dta", replace

	
	
*********************
* Weather sentiment *
*********************

* Import data from Excel to Stata
	// single-measure countries
		foreach x in AUS AUT CAN CHE ///
			DNK ESP FIN FRA ///
			ITA NZL SWE USA ///
			{
				clear all
				import excel "orig/sentiment/weather/annual_station_level_data_final/`x'.xlsx", sheet("final_series") firstrow
				save "data/`x'.dta", replace
			}
	// double-measure countries
		**** NOTE: measure 1 is in "final_series1", measure 2 is in "final_series2"
		foreach x in BEL DEU GBR IRL NLD ///
			{
				clear all
				import excel "orig/sentiment/weather/annual_station_level_data_final/`x'.xlsx", sheet("final_series1") firstrow
				save "data/`x'.dta", replace
			}
		
* Merge weather measures
	clear all
	use "data/AUS.dta"
	foreach x in AUT BEL CAN CHE ///
			DEU DNK ESP FIN FRA GBR ///
			IRL ITA NLD NZL SWE USA ///
		{
			merge m:m cou year using "data/`x'.dta"
			drop _merge
		}
		
* Delete individual series
	foreach x in AUS AUT BEL CAN CHE ///
			DEU DNK ESP FIN FRA GBR ///
			IRL ITA NLD NZL SWE USA ///
		{
			erase "data/`x'.dta"
		}
	drop if year < 1974
	drop if year > 2019
	save "data/weather.dta", replace
	
